from flask import Blueprint,jsonify,request
from flask import current_app,render_template
from sqlalchemy import *
from job.models import CateWC,Job
from job import db
report = Blueprint("report",__name__)

@report.route("/")
def index():
    return  render_template('report.html')

@report.route('qipao')
def qipao():
    data = db.session.execute("select pubtime,cnum,sum(num) from job group by cnum,pubtime,num").fetchall()
    data1=[]
    print(data)
    for i in data:
        if(i[1]=="少于50人"):
            data1.append([float(i[0].replace("-",".")),50,int(i[2])]) #发布时间(x轴),公司规模(y轴),人数(气泡大小)
        elif(i[1]=="50-150人"):
            data1.append([float(i[0].replace("-",".")), 150, int(i[2])])
        elif (i[1] == "150-500人"):
            data1.append([float(i[0].replace("-",".")), 500, int(i[2])])
        elif (i[1] == "500-1000人"):
            data1.append([float(i[0].replace("-",".")), 1000, int(i[2])])
        elif (i[1] == "1000-5000人"):
            data1.append([float(i[0].replace("-",".")), 5000, int(i[2])])
        elif (i[1] == "5000-10000人"):
            data1.append([float(i[0].replace("-",".")), 10000, int(i[2])])
        elif (i[1] == "10000人以上"):
            data1.append([float(i[0].replace("-",".")), 15000, int(i[2])])
    print(data1)
    return jsonify({
     "data":data1
    })

@report.route('time_num')#漏斗图 时间和招聘数量
def time_num():
    # data =db.session.execute("select substr(pubtime,1,2)as month,sum(num) from job group by  substr(pubtime,1,2)").fetchall()
    # data1=[]
    # for i in data:
    #     json = {}
    #     json['name']=i[0]
    #     json['value']=int(i[1])
    #     data1.append(json)
    data1= [{"name": "1月", "value": 1807}, {"name": "2月", "value": 75684}, {"name": "3月", "value": 301924},
     {"name": "4月", "value": 608959}, {"name": "5月", "value": 892723}]
    return jsonify(data1)

##不同月份不同规模企业的招聘数量
@report.route('cnum_pubtime_num')
def cnum_pubtime_num():
    # data = db.session.execute("select substr(pubtime,1,2) month,cnum,count(*) from job group by cname,substr(pubtime,1,2),cnum having(month=04 or month=05)").fetchall()
    # print(data)
    # data03=[] #三月份
    # data05=[] #五月份
    # data_qy=[] ##企业规模
    # data_m5_m3= []
    # for i in data:
    #     if(i[0]=="04"):
    #         data03.append(int(i[2]))
    #         data_qy.append(i[1])
    #     elif(i[0])=="05":
    #         data05.append(int(i[2]))
    # for i in range(len(data03)):
    #     data_m5_m3.append(round(((data05[i]-data03[i])/data03[i])*100,2))
    # xAxis2=[data_qy[6],data_qy[3],data_qy[2],data_qy[4],data_qy[0],data_qy[5],data_qy[1]]
    # data1 = [data_m5_m3[6], data_m5_m3[3], data_m5_m3[2], data_m5_m3[4], data_m5_m3[0], data_m5_m3[5], data_m5_m3[1]]
    # return jsonify({
    #     "xAxis":xAxis2,
    #     "data":data1
    # })
    return jsonify({
          "data": [
              22.74,
              22.66,
              21.06,
              15.29,
              11.55,
              28.14,
             20.88
          ],
          "xAxis": [
              "\u5c11\u4e8e50\u4eba",
              "50-150\u4eba",
              "150-500\u4eba",
              "500-1000\u4eba",
              "1000-5000\u4eba",
              "5000-10000\u4eba",
            "10000\u4eba\u4ee5\u4e0a",
          ]
        }
        )

#不同规模公司2-5月份招聘人数对比
@report.route('pubtime_cnum')
def pubtime_cnum():
    # data = db.session.execute("select pubtime month,cnum,sum(num) from job where pubtime <='05-19' group by pubtime,cnum;").fetchall()
    # data1=[]
    # data2=[]
    # data3=[]
    # data4=[]
    # data5=[]
    # data6=[]
    # data7=[]
    # guimo=[]
    # month=[]
    # for i in data:
    #     if (i[1] == "少于50人"):
    #         data1.append(int(i[2]))
    #         month.append(i[0])
    #     elif (i[1] == "50-150人"):
    #         data2.append(int(i[2]))
    #     elif (i[1] == "150-500人"):
    #         data3.append(int(i[2]))
    #     elif (i[1] == "500-1000人"):
    #         data4.append(int(i[2]))
    #     elif (i[1] == "1000-5000人"):
    #         data5.append(int(i[2]))
    #     elif (i[1] == "5000-10000人"):
    #         data6.append(int(i[2]))
    #     elif (i[1] == "10000人以上"):
    #         data7.append(int(i[2]))
    # guimo = ['少于50人','50-150人','150-500人','500-1000人','1000-5000人','5000-10000人','10000人以上']
    # return jsonify({
    #     "data1":data1,
    #     "data2":data2,
    #     "data3":data3,
    #     "data4":data4,
    #     "data5":data5,
    #     "data6":data6,
    #     "data7":data7,
    #     "month":month,
    #     "guimo":guimo,
    # })
    return jsonify({"data1":[2,9,20,27,40,72,72,124,134,128,319,151,145,128,266,106,261,244,274,267,280,357,288,258,363,400,366,403,512,384,334,850,702,905,1048,1047,429,321,1022,975,830,770,1218,753,484,1190,1505,1422,1316,2528,1659,1044,2620,3028,2801,3025,5531,3087,2903,530,1426,750,790,1311,748,608,1285,1118,2708,2552,3598,2586,2517,3562,3283,2619,2820,2862,2932,2497,3098,3145,2940,2971,3230,2862,2529,3366,3267,3074,3044,4273,2962,3368,3702,4290,3687,3804,1009,1019,892,933,755,1636,1503,1913,2193,1585,2976,3101,3429,3207,5338,2779,2430,9102,16243],"data2":[23,19,53,37,36,72,168,149,173,152,472,371,130,343,196,188,193,492,556,390,428,629,417,343,668,772,783,620,1116,607,512,1273,1488,1311,1897,1845,736,609,1968,1693,1415,1763,1964,1059,871,2135,2317,2477,2819,4163,1802,1948,5218,4771,4663,6827,10359,5445,4333,1062,3132,1536,1217,3052,977,679,1734,1641,6182,5303,5900,5076,4824,5254,5778,4988,5557,5606,5130,4929,5980,5607,5422,5670,6485,5329,5348,6392,6149,5941,6183,7299,5440,6158,6434,6994,6447,7373,1263,1127,1051,1301,894,2502,2422,2698,3400,1621,4713,5273,5837,5493,8433,4100,3464,16873,32814],"data3":[12,15,9,17,21,37,232,80,107,200,223,219,208,164,254,185,131,631,400,497,698,447,386,343,718,630,662,778,632,342,280,1188,1471,1077,1803,1598,606,640,1118,1324,951,1254,1650,697,507,1822,1678,2053,1829,3161,1961,1215,3538,3681,4630,4955,8064,4096,3093,957,2883,955,960,924,602,336,1054,1439,5530,4631,5341,4102,4166,4061,4619,4410,4466,4543,4415,3871,4520,4624,4790,4977,5192,4429,4071,5464,4607,4778,4736,6022,4577,4916,5830,5562,5479,6028,944,680,662,860,823,1827,1911,1947,1810,1000,3484,3893,4269,4813,6580,2838,2181,15724,30397],"data4":[1,6,24,15,27,4,21,22,157,90,136,76,38,48,133,79,376,196,274,311,174,158,53,175,244,422,318,384,123,214,563,513,375,1002,743,386,311,630,720,748,360,647,843,235,859,1258,1119,1050,1597,816,722,1775,1775,2273,2445,4167,1656,1748,458,1074,480,477,675,403,355,829,647,1980,3267,2452,1835,1920,1780,2086,2211,2117,1883,1843,1793,1966,2243,2145,2185,2069,1732,1681,2686,2173,2333,2034,2850,1925,2401,2531,2826,2261,2348,317,366,445,348,676,1168,1103,1271,902,517,1675,1918,2104,1959,2630,961,1042,6693,12993],"data5":[4,72,32,22,34,73,97,83,320,300,158,124,122,154,118,531,436,374,336,483,277,138,298,393,459,500,480,319,200,695,988,737,1324,910,572,399,988,580,595,535,582,497,348,1260,1084,1200,1455,2619,3051,1592,3679,2844,3380,3263,4429,1952,1625,569,1377,725,637,1164,592,215,683,704,2586,2513,2951,2088,1975,2110,2479,2374,2291,2118,2147,2077,2681,2715,2415,2476,2486,2262,2239,2776,2524,2538,2311,2916,2053,2560,3071,2582,2809,3017,420,361,246,748,425,1616,1154,1524,1366,521,2118,2245,2733,3079,3250,1504,1587,8897,16284],"data6":[3,14,6,38,3,48,71,13,3,30,10,245,168,81,27,92,183,35,22,122,105,89,118,127,43,40,134,424,111,176,153,136,31,167,106,147,55,133,135,49,351,301,179,370,249,259,61,412,440,805,766,698,482,368,83,241,69,74,181,41,64,88,136,404,485,611,307,396,374,396,511,455,480,319,348,609,417,410,370,469,381,449,474,521,403,519,486,368,398,653,551,404,675,153,105,47,58,67,238,183,192,303,562,480,877,610,544,838,381,197,2116,3486],"data7":[15,13,16,6,15,81,10,97,112,26,40,120,31,180,47,86,171,225,86,73,120,167,321,129,244,159,53,299,419,270,629,618,304,721,65,307,281,273,250,412,166,118,249,820,526,1397,640,797,686,1375,839,864,1179,1614,821,906,456,594,219,809,367,140,62,718,384,1367,1898,1331,1328,1044,1187,1261,1248,1282,1127,1202,1258,1270,1342,1228,1608,1291,1246,1283,1432,1449,1911,1276,1825,1232,1432,1779,1493,1365,1542,326,436,234,210,262,332,736,525,622,144,1083,989,1737,1248,1391,1149,522,4783,14334],"guimo":["\u5c11\u4e8e50\u4eba","50-150\u4eba","150-500\u4eba","500-1000\u4eba","1000-5000\u4eba","5000-10000\u4eba","10000\u4eba\u4ee5\u4e0a"],"month":["01-24","01-25","01-26","01-27","01-28","01-29","01-30","01-31","02-01","02-02","02-03","02-04","02-05","02-06","02-07","02-08","02-09","02-10","02-11","02-12","02-13","02-14","02-15","02-16","02-17","02-18","02-19","02-20","02-21","02-22","02-23","02-24","02-25","02-26","02-27","02-28","02-29","03-01","03-02","03-03","03-04","03-05","03-06","03-07","03-08","03-09","03-10","03-11","03-12","03-13","03-14","03-15","03-16","03-17","03-18","03-19","03-20","03-21","03-22","03-23","03-24","03-25","03-26","03-27","03-28","03-29","03-30","03-31","04-01","04-02","04-03","04-04","04-05","04-06","04-07","04-08","04-09","04-10","04-11","04-12","04-13","04-14","04-15","04-16","04-17","04-18","04-19","04-20","04-21","04-22","04-23","04-24","04-25","04-26","04-27","04-28","04-29","04-30","05-01","05-02","05-03","05-04","05-05","05-06","05-07","05-08","05-09","05-10","05-11","05-12","05-13","05-14","05-15","05-16","05-17","05-18","05-19"]})

#各种类型公司各个月份招聘数量
@report.route("ctype_month_num")
def ctype_month_num():
    # data = db.session.execute("select ctype,substr(pubtime,1,2),sum(num) from job where (ctype like '%国企%' or ctype like '%上市公司%' or ctype like '%创业公司%' or ctype like '%合资%' or ctype like '%外资%' or ctype like '%民营公司%') group by ctype,substr(pubtime,1,2);").fetchall()
    #
    # '''| ctype | substr(pubtime, 1, 2) | sum(num) |
    # +--------------+---------------------+----------+
    # | 上市公司 | 01 | 56 |'''
    # data1=[]
    # data2=[]
    # data3=[]
    # data4=[]
    # data5=[]
    # data6=[]
    # month=[]
    # for i in data:
    #     if(i[0]=="上市公司"):
    #         data1.append(int(i[2]))
    #         month.append(i[1])
    #     elif(i[0]=="国企"):
    #         data2.append(int(i[2]))
    #     elif(i[0]=="创业公司"):
    #         data3.append(int(i[2]))
    #     elif(i[0]=="合资"):
    #         data4.append(int(i[2]))
    #     elif(i[0]=="外资"):
    #         data5.append(int(i[2]))
    #     elif (i[0] == "民营公司"):
    #         data6.append(int(i[2]))
    # print(data1)
    # print(month)
    # gongsi=['上市公司','国企','创业公司','合资','外资','民营公司']
    # return jsonify({
    #     "data1":data1,
    #     "data2":data2,
    #     "data3":data3,
    #     "data4":data4,
    #     "data5":data5,
    #     "data6":data6,
    #     "month":month,
    #     "gongsi":gongsi
    # })
    return jsonify(
        {"data1": [56, 8291, 25398, 53719, 72197], "data2": [34, 4581, 19141, 29466, 36051],
         "data3": [57, 1504, 5322, 9053, 12572], "data4": [70, 4616, 19473, 42129, 53345],
         "data5": [123, 5935, 27271, 53356, 71806], "data6": [1465, 50185, 202388, 416162, 636000],
         "gongsi": ["\u4e0a\u5e02\u516c\u53f8", "\u56fd\u4f01", "\u521b\u4e1a\u516c\u53f8", "\u5408\u8d44",
                    "\u5916\u8d44", "\u6c11\u8425\u516c\u53f8"], "month": ["01", "02", "03", "04", "05"]}
    )


# 二、
# 各公司行业ctrade（如：酒店|旅游）9月-2月招聘数量变化柱状图
# 计算公式，ctrade字段按|切分，保留第一个词，比如 酒店|旅游  这个字段就只保留酒店，或者炸开炸成两行数据
# 然后   （9月该行业招聘人数-2月该行业招聘人数）/2月该行业招聘人数*100
@report.route("/ctrade_pubtime_num",methods=['get'])
def ctrade_pubtime_num():
    # data1 = db.session.execute('select t1.ctrade,(t1.num-t2.num)/t2.num*10 as num1 from (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) t1 where pubtime="09"  group by ctrade,pubtime)t1 join (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) ntable where pubtime="02"  group by ctrade,pubtime)t2 ON t1.ctrade=t2.ctrade order by num1 limit 5;').fetchall()
    # data2 = db.session.execute('select t1.ctrade,(t1.num-t2.num)/t2.num*10 as num1 from (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) t1 where pubtime="09"  group by ctrade,pubtime)t1 join (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) ntable where pubtime="02"  group by ctrade,pubtime)t2 ON t1.ctrade=t2.ctrade order by num1 desc  limit 35;').fetchall()
    #
    # data11=[]
    # data12=[]
    #
    # data21=[]
    # data22=[]
    # for i in data1:
    #     data11.append(i[0])
    #     data12.append(float(i[1]))
    #
    # for i in data2:
    #     data21.append(i[0])
    #     data22.append(float(i[1]))
    # data11=data11+data21
    # data12=data12+data22
    # return jsonify({
    #     "data11":data11,
    #     "data12": data12,
    # })
    return jsonify({
  "data11": [
    "\u8ba1\u7b97\u673a\u786c\u4ef6",
    "\u8ba1\u7b97\u673a\u670d\u52a1",
    "\u901a\u4fe1",
    "\u8ba1\u7b97\u673a\u8f6f\u4ef6",
    "\u7f51\u7edc\u6e38\u620f",
    "\u4e92\u8054\u7f51",
  ],
  "data12": [
    62.1,
    61.4,
    47.6,
    48.5,
    28.0,
    21.2
  ]
})

    # 二、
    # 各公司行业ctrade（如：酒店|旅游）9月-2月招聘数量变化柱状图
    # 计算公式，ctrade字段按|切分，保留第一个词，比如 酒店|旅游  这个字段就只保留酒店，或者炸开炸成两行数据
    # 然后   （9月该行业招聘人数-2月该行业招聘人数）/2月该行业招聘人数*100
@report.route("/ctrade_pubtime_num_low5", methods=['get'])
def ctrade_pubtime_num_low5():
        # data1 = db.session.execute('select t1.ctrade,(t1.num-t2.num)/t2.num*10 as num1 from (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) t1 where pubtime="09"  group by ctrade,pubtime)t1 join (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) ntable where pubtime="02"  group by ctrade,pubtime)t2 ON t1.ctrade=t2.ctrade order by num1 limit 5;').fetchall()
        # data2 = db.session.execute('select t1.ctrade,(t1.num-t2.num)/t2.num*10 as num1 from (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) t1 where pubtime="09"  group by ctrade,pubtime)t1 join (select ctrade,pubtime,sum(num)as num from(SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.ctrade, \'|\', b.help_topic_id + 1 ), \'|\',- 1 ) AS ctrade,SUBSTR(pubtime,1,2) as  pubtime,num FROM    `job` AS a  JOIN mysql.help_topic AS b ON b.help_topic_id < 1 ) ntable where pubtime="02"  group by ctrade,pubtime)t2 ON t1.ctrade=t2.ctrade order by num1 desc  limit 35;').fetchall()
        #
        # data11=[]
        # data12=[]
        #
        # data21=[]
        # data22=[]
        # for i in data1:
        #     data11.append(i[0])
        #     data12.append(float(i[1]))
        #
        # for i in data2:
        #     data21.append(i[0])
        #     data22.append(float(i[1]))
        # data11=data11+data21
        # data12=data12+data22
        # return jsonify({
        #     "data11":data11,
        #     "data12": data12,
        # })
        return jsonify({
            "data11": [
                "\u4fdd\u9669",
                "\u94f6\u884c",
                "\u6559\u80b2",
                "\u79df\u8d41\u670d\u52a1",
                "\u91d1\u878d",
            ],
            "data12": [
                -6.4,
                0.3,
                0.7,
                0.8,
                2.0,
            ]
        })




@report.route("/cate1_pubtime_num",methods=['get'])
def cate1_pubtime_num():
    # data = db.session.execute('select time1.cate1,(time1.sum-time2.sum)/time2.sum*10 num   from (select cate1,SUBSTR(pubtime,1,2)pubtime,sum(num) as sum from job where SUBSTR(pubtime,1,2)="09" group by cate1,SUBSTR(pubtime,1,2))time1 join (select cate1,SUBSTR(pubtime,1,2)pubtime,sum(num) as sum from job where SUBSTR(pubtime,1,2)="02" group by cate1,SUBSTR(pubtime,1,2))time2 ON time1.cate1=time2.cate1 order by num ;').fetchall()
    # data1=[]
    # data2=[]
    # for i in data:
    #     data1.append(i[0])
    #     data2.append(float(i[1]))
    # return jsonify({
    #     "data1":data1,
    #     "data2":data2
    # })
    return jsonify({
  "data1": [
    "\u6570\u636e",
    "\u534a\u5bfc\u4f53/\u82af\u7247",
    "\u8bbe\u8ba1",
    "\u6d4b\u8bd5",
    "\u6e38\u620f",
    "\u4eba\u5de5\u667a\u80fd",
    "\u901a\u4fe1\u6280\u672f\u5f00\u53d1\u53ca\u5e94\u7528",
    "\u4ea7\u54c1",
    "\u8fd0\u7ef4/\u6280\u672f\u652f\u6301",
    "\u540e\u7aef\u5f00\u53d1",
    "\u524d\u7aef\u5f00\u53d1",
    "\u79fb\u52a8\u5f00\u53d1",
  ],
  "data2": [
    -6.8,
    0.8,
    2.0,
    2.7,
    13.0,
    22.4,
    49.7,
    53.2,
    57.5,
    64.4,
    74.2,
    132.9,
  ]
}
)